using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using fuzhu;
using LKU8.shoukuan;
using System.ComponentModel;
using System.Text.RegularExpressions;

namespace fuzhu
{
    /// <summary>
    /// 万吉制造订单Excel导入助手
    /// 专门处理万吉制造订单Excel模板的导入
    /// </summary>
    public class WanJiExcelImportHelper
    {
        private static WriteLog wl = new WriteLog();

        /// <summary>
        /// 从Excel文件导入万吉制造订单数据
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>导入的订单数据列表</returns>
        public static BindingList<WanJi_Morder_View> ImportFromExcel(string filePath)
        {
            return ImportFromExcel(filePath, 0); // 默认使用第一个工作表
        }

        /// <summary>
        /// 从Excel文件的指定工作表导入万吉制造订单数据
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="sheetIndex">工作表索引</param>
        /// <returns>导入的订单数据列表</returns>
        public static BindingList<WanJi_Morder_View> ImportFromExcel(string filePath, int sheetIndex)
        {
            BindingList<WanJi_Morder_View> resultList = new BindingList<WanJi_Morder_View>();
            
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                if (sheetIndex >= workbook.NumberOfSheets)
                {
                    throw new Exception(string.Format("工作表索引 {0} 超出范围，文件只有 {1} 个工作表",sheetIndex,workbook.NumberOfSheets));
                }

                ISheet sheet = workbook.GetSheetAt(sheetIndex);
                
                if (sheet == null)
                {
                    throw new Exception("Excel文件中没有找到工作表");
                }

                // 获取标题行（第二行，索引为1）
                IRow headerRow = sheet.GetRow(1);
                if (headerRow == null)
                {
                    throw new Exception("Excel文件中没有找到标题行（第二行）");
                }

                // 解析标题行，建立列名到列索引的映射
                Dictionary<string, int> columnMapping = ParseHeaderRow(headerRow);

                // 验证必需的列是否存在
                ValidateRequiredColumns(columnMapping);

                // 从第三行开始读取数据（索引为2）
                int rowIndex = 2;
                int serialNumber = 1;

                while (rowIndex <= sheet.LastRowNum)
                {
                    IRow row = sheet.GetRow(rowIndex);
                    if (row == null)
                    {
                        rowIndex++;
                        continue;
                    }

                    // 检查工单号是否为空，如果为空则停止读取
                    string moCode = GetCellValue(row, columnMapping, "工单号");
                    if (string.IsNullOrEmpty(moCode))
                    {
                        break; // 工单号为空，停止读取
                    }

                    // 创建订单视图对象
                    WanJi_Morder_View orderView = new WanJi_Morder_View();
                    
                    //// 设置序号
                    //orderView.cRowNo = serialNumber;
                    
                    // 填充数据
                    FillOrderData(orderView, row, columnMapping);
                    
                    resultList.Add(orderView);
                    
                    rowIndex++;
                    serialNumber++;
                }

                return resultList;
            }
            catch (Exception ex)
            {
                wl.WriteLogs("导入Excel时发生错误: " + ex.Message);
                throw new Exception("导入Excel文件失败：" + ex.Message, ex);
            }
        }

        /// <summary>
        /// 解析标题行，建立列名到列索引的映射
        /// </summary>
        /// <param name="headerRow">标题行</param>
        /// <returns>列名到列索引的映射字典</returns>
        private static Dictionary<string, int> ParseHeaderRow(IRow headerRow)
        {
            Dictionary<string, int> columnMapping = new Dictionary<string, int>();
            
            for (int i = 0; i <= headerRow.LastCellNum; i++)
            {
                ICell cell = headerRow.GetCell(i);
                if (cell != null)
                {
                    string cellValue = cell.ToString().Trim();
                    if (!string.IsNullOrEmpty(cellValue))
                    {
                        // 处理可能包含括号的列名，如"订单批量（Pcs）"
                        columnMapping[cellValue] = i;
                        
                        // 为了兼容性，也添加去掉括号内容的版本
                        string simplifiedName = Regex.Replace(cellValue, @"[（(].*?[）)]", "").Trim();
                        if (!columnMapping.ContainsKey(simplifiedName) && simplifiedName != cellValue)
                        {
                            columnMapping[simplifiedName] = i;
                        }
                    }
                }
            }

            return columnMapping;
        }

        /// <summary>
        /// 验证必需的列是否存在
        /// </summary>
        /// <param name="columnMapping">列名映射</param>
        private static void ValidateRequiredColumns(Dictionary<string, int> columnMapping)
        {
            string[] requiredColumns = { "工单号", "订单日期", "订单批量（Pcs）" };
            List<string> missingColumns = new List<string>();

            foreach (string required in requiredColumns)
            {
                if (!columnMapping.ContainsKey(required))
                {
                    // 对于订单批量，也检查简化版本
                    if (required == "订单批量（Pcs）" && columnMapping.ContainsKey("订单批量"))
                    {
                        continue;
                    }
                    missingColumns.Add(required);
                }
            }

            if (missingColumns.Count > 0)
            {
                throw new Exception("Excel模板缺少必需的列：" + string.Join(", ", missingColumns.ToArray()));
            }
        }

        /// <summary>
        /// 从行中获取指定列的值
        /// </summary>
        /// <param name="row">数据行</param>
        /// <param name="columnMapping">列名映射</param>
        /// <param name="columnName">列名</param>
        /// <returns>单元格值</returns>
        private static string GetCellValue(IRow row, Dictionary<string, int> columnMapping, string columnName)
        {
            if (!columnMapping.ContainsKey(columnName))
            {
                return "";
            }

            int columnIndex = columnMapping[columnName];
            ICell cell = row.GetCell(columnIndex);
            
            if (cell == null)
            {
                return "";
            }

            try
            {
                switch (cell.CellType)
                {
                    case CellType.STRING:
                        return cell.StringCellValue.Trim();
                    case CellType.NUMERIC:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue.ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            return cell.NumericCellValue.ToString();
                        }
                    case CellType.BOOLEAN:
                        return cell.BooleanCellValue.ToString();
                    case CellType.FORMULA:
                        // 改进公式处理逻辑
                        return GetFormulaValue(cell);
                    case CellType.BLANK:
                        return "";
                    case CellType.ERROR:
                        wl.WriteLogs(string.Format("单元格错误: {0}",cell.ErrorCellValue));
                        return "";
                    default:
                        return "";
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs(string.Format("获取单元格值错误: {0}",ex.Message));
                return "";
            }
        }

        /// <summary>
        /// 获取公式单元格的值
        /// </summary>
        /// <param name="cell">公式单元格</param>
        /// <returns>公式计算结果</returns>
        private static string GetFormulaValue(ICell cell)
        {
            try
            {
                // 尝试获取公式的计算结果
                IFormulaEvaluator evaluator = cell.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
                CellValue cellValue = evaluator.Evaluate(cell);
                
                switch (cellValue.CellType)
                {
                    case CellType.STRING:
                        return cellValue.StringValue.Trim();
                    case CellType.NUMERIC:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return DateUtil.GetJavaDate(cellValue.NumberValue).ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            return cellValue.NumberValue.ToString();
                        }
                    case CellType.BOOLEAN:
                        return cellValue.BooleanValue.ToString();
                    case CellType.ERROR:
                        wl.WriteLogs(string.Format("公式计算错误: {0}",cellValue.ErrorValue));
                        return "";
                    default:
                        return "";
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs(string.Format("公式计算异常: {0}",ex.Message));
                
                // 如果公式计算失败，尝试获取缓存结果
                try
                {
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.STRING:
                            return cell.StringCellValue.Trim();
                        case CellType.NUMERIC:
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue.ToString("yyyy-MM-dd");
                            }
                            else
                            {
                                return cell.NumericCellValue.ToString();
                            }
                        case CellType.BOOLEAN:
                            return cell.BooleanCellValue.ToString();
                        default:
                            return "";
                    }
                }
                catch
                {
                    wl.WriteLogs("无法获取公式结果，返回空值");
                    return "";
                }
            }
        }

        /// <summary>
        /// 填充订单数据
        /// </summary>
        /// <param name="orderView">订单视图对象</param>
        /// <param name="row">数据行</param>
        /// <param name="columnMapping">列名映射</param>
        private static void FillOrderData(WanJi_Morder_View orderView, IRow row, Dictionary<string, int> columnMapping)
        {
            try
            {
                // 根据gridView1的GridColumn的Caption和FieldName进行映射
                
                string cRowNoValue = GetCellValue(row, columnMapping, "序号");
                if (!string.IsNullOrEmpty(cRowNoValue))
                {
                    // 去掉可能的非数字字符
                    string numericValue = Regex.Replace(cRowNoValue, @"[^\d.]", "");
                    int parsedQty; 

                    if (int.TryParse(numericValue, out parsedQty))
                    {
                        orderView.cRowNo = parsedQty;
                    } 
                    else
                    {
                        orderView.cRowNo = 0;
                    }
                }
                else
                {
                    orderView.cRowNo = 0;
                }

                orderView.cMoCode = GetCellValue(row, columnMapping, "工单号");
                orderView.cSoCode = GetCellValue(row, columnMapping, "销售订单号");
                
                orderView.cCusName = GetCellValue(row, columnMapping, "客户");
                orderView.cXiLie = GetCellValue(row, columnMapping, "系列");
                orderView.cOperation = GetCellValue(row, columnMapping, "工序");
                orderView.cJiZhongName = GetCellValue(row, columnMapping, "机种名称");
                orderView.cCusOrderCode = GetCellValue(row, columnMapping, "订单号");
                orderView.cDangWei = GetCellValue(row, columnMapping, "档位");
                orderView.cMemo = GetCellValue(row, columnMapping, "备注");
                
                // 处理订单日期
                string dateValue = GetCellValue(row, columnMapping, "订单日期");
                if (!string.IsNullOrEmpty(dateValue))
                {
                    DateTime parsedDate;
if (DateTime.TryParse(dateValue, out parsedDate))
{
    orderView.dDate = parsedDate;
}
else
{
    orderView.dDate = DateTime.MinValue;
}
                }
                else
                {
                    orderView.dDate = DateTime.MinValue;
                }

                // 处理订单批量（考虑括号问题）
                string qtyValue = GetCellValue(row, columnMapping, "订单批量（Pcs）");
                if (string.IsNullOrEmpty(qtyValue))
                {
                    qtyValue = GetCellValue(row, columnMapping, "订单批量");
                }
                
                if (!string.IsNullOrEmpty(qtyValue))
                {
                    // 去掉可能的非数字字符
                    string numericValue = Regex.Replace(qtyValue, @"[^\d.]", "");
                    int parsedQty;
                    double parsedDouble;

                    if (int.TryParse(numericValue, out parsedQty))
                    {
                        orderView.iQty = parsedQty;
                    }
                    else if (double.TryParse(numericValue, out parsedDouble))
                    {
                        orderView.iQty = (int)parsedDouble;
                    }
                    else
                    {
                        orderView.iQty = 0;
                    }
                }
                else
                {
                    orderView.iQty = 0;
                }

                // 处理U8产品编码列
                orderView.cInvCodeSMT = GetCellValue(row, columnMapping, "U8-SMT产品编码");
                orderView.cInvCodeDIP = GetCellValue(row, columnMapping, "U8-DIP产品编码");
                orderView.cInvCodeHou = GetCellValue(row, columnMapping, "U8-后道产品编码");

                // 初始化默认值
                orderView.chk = false;
                orderView.bU8 = false;
            }
            catch (Exception ex)
            {
                wl.WriteLogs("填充订单数据时发生错误: " + ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 获取Excel文件中所有工作表的名称
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>工作表名称列表</returns>
        public static List<string> GetSheetNames(string filePath)
        {
            List<string> sheetNames = new List<string>();
            
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    sheetNames.Add(workbook.GetSheetName(i));
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs("获取工作表名称时发生错误: " + ex.Message);
                throw;
            }

            return sheetNames;
        }

        /// <summary>
        /// 验证Excel文件格式是否正确
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>验证结果信息</returns>
        public static string ValidateExcelFormat(string filePath)
        {
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                ISheet sheet = workbook.GetSheetAt(0);
                if (sheet == null)
                {
                    return "Excel文件中没有工作表";
                }

                // 检查是否有足够的行数
                if (sheet.LastRowNum < 2)
                {
                    return "Excel文件数据不足，至少需要3行（标题行+数据行）";
                }

                // 检查第二行是否有标题
                IRow headerRow = sheet.GetRow(1);
                if (headerRow == null)
                {
                    return "第二行（标题行）不存在";
                }

                // 解析标题行
                Dictionary<string, int> columnMapping = ParseHeaderRow(headerRow);
                
                // 验证必需列
                try
                {
                    ValidateRequiredColumns(columnMapping);
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }

                return "Excel格式验证通过";
            }
            catch (Exception ex)
            {
                wl.WriteLogs("验证Excel格式时发生错误: " + ex.Message);
                return "验证Excel格式失败：" + ex.Message;
            }
        }
    }
}